import pandas as pd
import pyodbc
import pypyodbc
from openpyxl import Workbook
import datetime

# 数据库地址字典，用于存储每个车间各条线的数据库地址
database_paths = {
    "4车间": {
        "1A": r"\\10.8.178.47\halm_bifacial\PVCTData",
        "1B": r"\\10.8.176.92\halm_bifacial\PVCTData",
        "2A": r"\\10.8.141.93\halm_bifacial\PVCTData",
        "2B": r"\\10.8.141.233\halm_bifacial\PVCTData",
        "3A": r"\\10.8.176.95\halm_bifacial\PVCTData",
        "3B": r"\\10.8.141.96\halm_bifacial\PVCTData",
        "4A": r"\\10.8.141.97\halm_bifacial\PVCTData",
        "4B": r"\\10.8.141.99\halm_bifacial\PVCTData",
        "5A": r"\\10.8.141.98\halm_bifacial\PVCTData",
        "5B": r"\\10.8.141.206\halm_bifacial\PVCTData",
        "6A": r"\\10.8.141.100\halm_bifacial\PVCTData",
        "6B": r"\\10.8.141.102\halm_bifacial\PVCTData",
        "7A": r"\\10.8.137.226\halm_bifacial\PVCTData",
        "7B": r"\\10.8.178.187\halm_bifacial\PVCTData",
        "8A": r"\\10.8.176.105\halm_bifacial\PVCTData",
        "8B": r"\\10.8.141.106\halm_bifacial\PVCTData",
    },
    "5车间": {
        "1A": r"\\10.8.138.184\Halm\PVCTData",
        "1B": r"\\10.8.138.182\Halm\PVCTData",
        "2A": r"\\10.8.138.188\Halm\PVCTData",
        "2B": r"\\10.8.138.183\Halm\PVCTData",
        "3A": r"\\10.8.138.187\Halm\PVCTData",
        "3B": r"\\10.8.138.186\Halm\PVCTData",
        "4A": r"\\10.8.138.189\Halm\PVCTData",
        "4B": r"\\10.8.138.181\Halm\PVCTData",
        "5A": r"\\10.8.138.248\Halm\PVCTData",
        "5B": r"\\10.8.138.190\Halm\PVCTData",
        "6A": r"\\10.8.138.192\Halm\PVCTData",
        "6B": r"\\10.8.138.193\Halm\PVCTData",
        "7A": r"\\10.8.138.195\Halm\PVCTData",
        "7B": r"\\10.8.138.194\Halm\PVCTData",
        "8A": r"\\10.8.138.197\Halm\PVCTData",
        "8B": r"\\10.8.138.196\Halm\PVCTData",
    }
}

# 建立一个新的 Excel 文件
app = Workbook()
# 定义一个空的 DataFrame，用于存储符合条件的数据
all_data = pd.DataFrame()
nian=2023
month=3
# 遍历每个车间
for workshop in ["4车间"]:
    for day in (range(19,22)):
        for line in("1A","1B","2A","2B","3A","3B","4A","4B","5A","5B","6A","6B","7A","7B","8A","8B"):
            path_a=database_paths[workshop][line]
            for ban in ("A","B"):
                path_s=f"{path_a}\{nian}-{month}-{day}-{line}-{ban}.mdb" # 数据库文件路径
                data_s=f"{nian}-{month}-{day}-{line}-{ban}"
                print(path_s)
                try:
                    # 连接数据库并读取数据
                    cnn = pypyodbc.win_connect_mdb('Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ='+ path_s)
                    sql = "select eta from halm_results where BIN_Comment='Eta22.8%' "
                    data = pd.read_sql(sql,cnn)
                    # 将读取到的数据连接到已有数据的后面
                    all_datas=pd.Series([data_s]*len(data))
                    data['data']=all_datas
                    all_data = pd.concat([all_data, data], ignore_index=True)
                    # 将符合条件的数据写入 Excel 文件中的新 sheet
                    all_data.to_excel('四厂3月19到21.xlsx', index=False)
                except Exception as e:
                    print(e)
